import sqlite3
import urllib.request
from sqlite3.dbapi2 import Connection
from typing import List, Tuple
from lxml import etree


class SpellDef:
    spell_name_cn: str
    spell_name_en: str
    gcd: int
    spell_id: int

    def __init__(self):
        self.spell_id = 0
        self.gcd = 0
        self.spell_name_en = ''
        self.spell_name_cn = ''


spell_def_list: List[SpellDef] = []


def fetch_all_data():
    conn: Connection = sqlite3.connect('J:/new_code/ClickerCode/Clicker/Clicker/demo.db')
    cur = conn.cursor()
    cur.execute(
        "SELECT main.SpellDef.spell_id, SpellDef.gcd, SpellDef.spell_name_en, SpellDef.spell_name_cn FROM SpellDef;")
    rows: List[tuple[int, int, str, str]] = cur.fetchall()
    if len(rows) > 0:
        for row in rows:
            spell_def = SpellDef()
            spell_def.gcd = row[1]
            spell_def.spell_id = row[0]
            spell_def.spell_name_en = row[2]
            spell_def.spell_name_cn = row[3]
            spell_def_list.append(spell_def)
    # 找到所有name为空的项目,进行查询,然后写入库
    for spell_def in spell_def_list:
        if spell_def.spell_name_cn == "" or spell_def.spell_name_en == "":
            print("spell id: %d" % spell_def.spell_id)
            # http 查询 对应id 的英文名 和 中文名
            spell_gcd, spell_en_name, spell_cn_name = fetch_spell_config(spell_def.spell_id)
            if spell_gcd is not None and "" != spell_en_name and "" != spell_cn_name:
                spell_def.spell_name_en = spell_en_name
                spell_def.spell_name_cn = spell_cn_name
                spell_def.gcd = spell_gcd
                sql = "UPDATE SpellDef SET spell_name_cn = \"%s\", spell_name_en = \"%s\", gcd = %d " \
                      "WHERE spell_id = %d" % (
                          spell_def.spell_name_cn, spell_def.spell_name_en, spell_def.gcd, spell_def.spell_id)
                cur.execute(sql)
                # Save (commit) the changes
                conn.commit()
    conn.close()


def fetch_spell_config(spell_id: int) -> Tuple[int, str, str]:
    url: str = 'http://www.wowhead.com/spell=%d' % (spell_id,)
    response = urllib.request.urlopen(url)  # 打开url地址,并返回一个 HTTPRespone实例
    html = response.read().decode('utf-8')  # 调用实例的 read()方法，并用 utf-8进行解码处理。就得出完整的百度的HTML文件
    # print(html)
    root = etree.HTML(html)  # type:etree._Element
    xpath_spell_name = "//div[@class='text']/noscript/table/tr/td/b/text()"
    td_spell_name: etree._Element = root.xpath(xpath_spell_name)
    if len(td_spell_name) == 0:
        xpath_spell_name = "//div[@class='text']/noscript/table/tr/td/table/tr/td/b/text()"
        td_spell_name: etree._Element = root.xpath(xpath_spell_name)
    if len(td_spell_name) == 0:
        xpath_spell_name = "//div[@class='text']/noscript/b/text()"
        td_spell_name: etree._Element = root.xpath(xpath_spell_name)
    spell_name: str = td_spell_name[0]
    print("get spell en name: %s" % spell_name)
    xpath_gcd = "//div[@class='text']/table[@class='grid auto-width']/tr[6]/td/text()"
    td_gcd: etree._Element = root.xpath(xpath_gcd)
    data_split = str(td_gcd[0]).split()
    spell_gcd = float(data_split[0])
    print("get spell gcd : %f" % (spell_gcd,))
    url_cn: str = 'http://cn.wowhead.com/spell=%d' % (spell_id,)
    response = urllib.request.urlopen(url_cn)  # 打开url地址,并返回一个 HTTPRespone实例
    html = response.read().decode('utf-8')  # 调用实例的 read()方法，并用 utf-8进行解码处理。就得出完整的百度的HTML文件
    # print(html)
    root = etree.HTML(html)  # type:etree._Element
    xpath_spell_name = "//div[@class='text']/noscript/table/tr/td/b/text()"
    td_spell_cn_name: etree._Element = root.xpath(xpath_spell_name)
    if len(td_spell_cn_name) == 0:
        xpath_spell_name = "//div[@class='text']/noscript/table/tr/td/table/tr/td/b/text()"
        td_spell_cn_name: etree._Element = root.xpath(xpath_spell_name)
    if len(td_spell_cn_name) == 0:
        xpath_spell_name = "//div[@class='text']/noscript/b/text()"
        td_spell_cn_name: etree._Element = root.xpath(xpath_spell_name)
    spell_cn_name: str = td_spell_cn_name[0]
    print("get spell cn name: %s" % spell_cn_name)
    return int(spell_gcd * 10), spell_name, spell_cn_name


def main():
    fetch_all_data()


if __name__ == '__main__':
    main()
